﻿using MetroDataQuery.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MetroDataQuery.Utility
{
    public class MonitorPointsQuery
    {

        /// <summary>
        /// 获取工程的某个测项,某个位置的初始值,注意,初始值不能按照天数来进行一组查询,有可能一组数据初始值不在同一天
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <returns></returns>
        public static List<MonitorPoints> GetOriginResults(int prjId, string monitorType, string location)
        {
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, false);

            using (var db = new MonitorProjectsEntities())
            {
                var sqlNames = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);

                var sql = string.Format(@"select {0}.* from {0}, 
	                (select Name,Min(datetime) as DateTime from 
	                (select tb1.Name,tb2.DateTime,tb2.Value from (select Name from {0} where PrjId=@p0 group by Name) as tb1
	                left join 
	                (select {0}.Name,{0}.Value,{0}.DateTime from {0} where {0}.PrjId=@p0) as tb2 
	                on (tb1.Name = tb2.Name) group by tb1.Name,tb2.DateTime,tb2.Value ) as tb
	                group by tb.Name) as mx
	                where {0}.PrjId=@p0 and {0}.Name=mx.Name and {0}.DateTime=mx.DateTime and {0}.Name in ({1})", tableName, sqlNames);
                return db.Database.SqlQuery<MonitorPoints>(sql, prjId).ToList();
            }

        }



        /// <summary>
        /// 获取一组点的变化量
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="ptNames"></param>
        /// <param name="tableName"></param>
        /// <param name="dateStr"></param>
        /// <param name="isResult"></param>
        /// <param name="dateType"></param>
        /// <returns></returns>
        public static List<MonitorPoints> GetPointsValueByDateTime(int prjId, List<string> ptNames, string tableName, string dateStr,
            bool isResult = false, string dateType = "date")
        {
            var ptNamesStr = String.Join(",", ptNames.Select(t => "'" + t + "'").ToList());
            using (var db = new MonitorProjectsEntities())
            {
                var sql = "";
                if (dateType == "date")
                {
                    sql = String.Format(@"select * from {0} where PrjId=@p0 
                        and cast(DateTime as date)=@p1 and Name in ({1}) order by Name"
                    , tableName, ptNamesStr);
                }
                else if (dateType == "datetime")
                {
                    sql = String.Format(@"select * from {0} where PrjId=@p0 
                        and DateTime=@p1 and Name in ({1}) order by Name"
                                   , tableName, ptNamesStr);
                }
                var list = db.Database.SqlQuery<MonitorPoints>(sql, prjId, dateStr).ToList();
                var finalResult = (from n in ptNames
                                   join p in list on n equals p.Name
                                       into gs
                                   from g in gs.DefaultIfEmpty()
                                   select new MonitorPoints()
                                   {
                                       DateTime = DateTime.Parse(dateStr),
                                       Name = n,
                                       PrjId = prjId,
                                       Value = g == null ? double.NaN : Math.Round(g.Value.Value, 4),
                                   }).ToList();
                return finalResult;
            }
        }

        /// <summary>
        /// 按照给定的日期获取一组点的观测值
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <param name="dateStr"></param>
        /// <param name="isResult"></param>
        /// <param name="dateType">datetime精确到时分秒或者只是date匹配日期部分</param>
        /// <returns></returns>
        private static List<MonitorPoints> GetPointsValueByDateTime(int prjId, string monitorType,
            string location, string dateStr, bool isResult = false, string dateType = "date")
        {
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, isResult);
            var ptNamesStr = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            var ptNames = PointsDictionaryQuery.GetPointNames(prjId, monitorType, location);
            return MonitorPointsQuery.GetPointsValueByDateTime(prjId, ptNames, tableName, dateStr, isResult, dateType);
        }


        /// <summary>
        /// 按照给定的日期获取一组点的观测值,本函数只匹配日期,只要年月日相等,就能找到匹配项
        /// </summary>
        /// <param name="prjId">工程ID</param>
        /// <param name="monitorType">监测类型</param>
        /// <param name="location">监测位置</param>
        /// <param name="dateStr">观测时间,不指定的话默认为初始值时间</param>
        /// <returns>监测点的观测值集合</returns>
        public static List<MonitorPoints> GetPointsValueByDate(int prjId, string monitorType,
            string location, string dateStr, bool isResult = false)
        {
            return MonitorPointsQuery.GetPointsValueByDateTime(prjId, monitorType, location, dateStr, isResult, "date");
        }

        /// <summary>
        /// 按照给定的日期获取一组点的观测值,本函数匹配年月日,时分秒
        /// </summary>
        /// <param name="prjId">工程ID</param>
        /// <param name="monitorType">监测类型</param>
        /// <param name="location">监测位置</param>
        /// <param name="dateStr">观测时间,不指定的话默认为初始值时间</param>
        /// <returns>监测点的观测值集合</returns>
        public static List<MonitorPoints> GetPointsValueByTime(int prjId, string monitorType,
            string location, string dateStr, bool isResult = false)
        {
            return MonitorPointsQuery.GetPointsValueByDateTime(prjId, monitorType, location, dateStr, isResult, "datetime");
        }
        /// <summary>
        /// 返回指定的日期下,有多少个观测数据
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <param name="dateStr"></param>
        /// <returns></returns>
        public static List<ContentItem> GetPointsCountByTime(int prjId, string monitorType,
            string location, string order = "desc")
        {
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, false);
            var ptNames = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);

            using (var db = new MonitorProjectsEntities())
            {
                var sql = String.Format(@"select COUNT(Name) as Count,{0}.DateTime as DateTime  from {0}, 
                        (select DateTime from {0} where PrjId=@p0 group by DateTime) as dates
                        where PrjId=@p0 and {0}.Name in ({1}) 
                    and  {0}.DateTime=dates.DateTime group by {0}.DateTime order by {0}.DateTime {2}
                        ", tableName, ptNames, order);
                return db.Database.SqlQuery<ContentItem>(sql, prjId).ToList();
            }
        }
        public class ContentItem
        {
            public DateTime DateTime { get; set; }
            public int Count { get; set; }
        }
        /// <summary>
        /// 根据给定的一组点名获取点的数据
        /// </summary>
        /// <param name="prjId">工程id</param>
        /// <param name="names">点名列表</param>
        /// <param name="dateStr">时间</param>
        /// <param name="isResult">是否是累计数据</param>
        /// <returns>数据列表,当没有查询到时,可能包含null的数据</returns>
        public static List<MonitorPoints> GetPointsValuesByNames(int prjId, string[] names, string dateStr, bool isResult = false)
        {
            var dictList = PointsDictionaryQuery.GetDictPointByNameList(prjId, names.ToList());
            var results = new List<MonitorPoints>();
            foreach (var dict in dictList)
            {
                var result = MonitorPointsQuery.GetPointsValueByTime(prjId, dict.MonitorType, dict.Location, dateStr, isResult);
                results.AddRange(results);
            }
            var time = DateTime.Parse(dateStr);
            var list = from n in names
                       join p in results on n equals p.Name into gs
                       select new MonitorPoints()
                       {
                           Name = n,
                           DateTime = time,
                           PrjId = prjId,
                           Value = gs.Count() > 0 ? gs.First().Value : double.NaN
                       };
            return list.ToList();
        }


        /// <summary>
        /// 获取某一点的初始值
        /// </summary>
        /// <param name="prjId">工程id</param>
        /// <param name="tableName">表名</param>
        /// <param name="ptName">点名</param>
        /// <returns></returns>
        public static MonitorPoints GetOrigin(int prjId, string tableName, string ptName)
        {
            using (var db = new MonitorProjectsEntities())
            {
                var sql = String.Format("select top 1 * from {0} where PrjId=@p0 and Name=@p1 order by DateTime", tableName);
                var item = db.Database.SqlQuery<MonitorPoints>(sql, prjId, ptName).SingleOrDefault();
                return item;
            }
        }





        /// <summary>
        /// 获取一个监测类型监测位置的所有时间列表
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <param name="startDateStr"></param>
        /// <param name="endDateStr"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static List<DateTime?> GetMonitorDateTimeList(int prjId, string monitorType,
            string location = "", string startDateStr = "", string endDateStr = "", int count = -1)
        {
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, true);
            var ptNames = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            using (var db = new MonitorProjectsEntities())
            {
                count = count <= 0 ? 9999999 : count;
                startDateStr = startDateStr.Length == 0 ? "1900-1-1" : startDateStr;
                endDateStr = endDateStr.Length == 0 ? DateTime.Now.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss") : endDateStr;
                var sql = String.Format(@"select Top {0} DateTime from {1} where Name in ({2}) and 
                                            DateTime>=@p0 and DateTime<=@p1 and PrjId=@p2 group by DateTime order by DateTime desc",
                    count, tableName, ptNames);
                var timeList = db.Database.SqlQuery<DateTime?>(sql, startDateStr, endDateStr, prjId);
                return timeList.ToList();
            }
        }


        /// <summary>
        /// 根据给定的个数,返回一个监测项观测数据的日期加时间倒序列表
        /// </summary>
        /// <param name="prjId">工程id</param>
        /// <param name="monitorType">监测类型</param>
        /// <param name="location">监测位置</param>
        /// <param name="startDateStr">开始时间</param>
        /// <param name="endDateStr">结束时间</param>
        /// <param name="count">期望返回的个数,-1为全部,如果时间个数小于指定的个数,以实际个数为准</param>
        /// <returns>时间列表字符串</returns>
        public static IEnumerable<string> GetMonitorTimeList(int prjId, string monitorType,
            string location = "", string startDateStr = "", string endDateStr = "", int count = -1)
        {

            var timeList = MonitorPointsQuery.
                                GetMonitorDateTimeList(prjId, monitorType, location, startDateStr, endDateStr, count).
                                Select(t => t.Value.ToString("yyyy-MM-dd HH:mm:ss")).ToList();
            return timeList;
        }

        /// <summary>
        /// 根据给定的个数,返回一个监测项观测数据的日期倒序列表
        /// </summary>
        /// <param name="prjId">工程id</param>
        /// <param name="monitorType">监测类型</param>
        /// <param name="location">监测位置</param>
        /// <param name="startDateStr">开始时间</param>
        /// <param name="endDateStr">结束时间</param>
        /// <param name="count">期望返回的个数,-1为全部,如果时间个数小于指定的个数,以实际个数为准</param>
        /// <returns>时间列表字符串</returns>
        public static IEnumerable<string> GetMonitorDateList(int prjId, string monitorType,
            string location = "", string startDateStr = "", string endDateStr = "", int count = -1)
        {

            var timeList = MonitorPointsQuery.
                                GetMonitorDateTimeList(prjId, monitorType, location, startDateStr, endDateStr, count).
                                Select(t => t.Value.ToString("yyyy-MM-dd")).GroupBy(p => p).Select(p => p.Key).ToList();
            return timeList;
        }


        /// <summary>
        /// 获取单点的历史记录,不给时间,默认取最近10次
        /// </summary>
        /// <param name="prjId">工程id</param>
        /// <param name="name">名称</param>
        /// <param name="startDate">起始时间</param>
        /// <param name="endDate">终止时间</param>
        /// <param name="count">数量</param>
        /// <param name="isResult">是否返回累积结果</param>
        /// <returns></returns>
        public static List<MonitorPoints> GetSinglePointsByName(int prjId, string name, string startDate = "",
                string endDate = "", int count = -1, bool isResult = true)
        {
            var info = PointsDictionaryQuery.GetDictPointByName(prjId, name);
            var table = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, info.MonitorType, isResult);
            if (endDate == "")
            {
                count = 10;
            }
            else
            {
                count = 999999;
            }
            var takeTen = count == 10 ? true : false;
            startDate = startDate == "" ? "2000-1-1" : startDate;
            endDate = endDate == "" ? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") : endDate;


            using (var db = new MonitorProjectsEntities())
            {
                if (!takeTen)
                {

                    var sql = string.Format(@"select top {0} * from {1} where PrjId=@p0 
                                    and DateTime>=@p1 and DateTime<=@p2 and Name=@p3 order by DateTime", count, table);
                    return db.Database.SqlQuery<MonitorPoints>(sql, prjId, startDate, endDate, name).ToList();
                }
                else
                {
                    var sql = string.Format(@"select top {0} * from {1} where PrjId=@p0 
                                    and DateTime>=@p1 and DateTime<=@p2 and Name=@p3 order by DateTime desc", count, table);
                    return db.Database.SqlQuery<MonitorPoints>(sql, prjId, startDate, endDate, name).Reverse().ToList();
                }
            }
        }


        /// <summary>
        /// 获取一个测项一个位置的初始值时间
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <returns></returns>
        public static string GetOriginTime(int prjId, string monitorType, string location)
        {
            var sqlArray = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, false);
            using (var db = new MonitorProjectsEntities())
            {
                var sql = string.Format(@"select min(DateTime) from {0} 
                            where Name in ({1}) and PrjId=@p0 group by DateTime order by DateTime", tableName, sqlArray);
                var date = db.Database.SqlQuery<DateTime>(sql, prjId).FirstOrDefault().ToString("yyyy-MM-dd");
                return date;
            }
        }



    }
}
